# L3_2.2 Lösung Datenbankabfragen Fahrradvermietung

# 1	
SELECT fahrraeder.fahrradnr, rahmennr
FROM fahrraeder, vermietungen
where fahrraeder.fahrradnr = vermietungen.fahrradnr
and vermietnr =1;

# 2	
SELECT fahrraeder.fahrradnr, rahmennr, bezeichnung
FROM fahrraeder, vermietungen, modelle
WHERE fahrraeder.fahrradnr = vermietungen.fahrradnr
AND fahrraeder.modellnr = modelle.modellnr
AND vermietnr =1;

# 3	
SELECT fahrraeder.fahrradnr, rahmennr, bezeichnung, vorname, nachname, ort
FROM fahrraeder, vermietungen, modelle, kunden, orte
WHERE fahrraeder.fahrradnr = vermietungen.fahrradnr
AND vermietungen.kundennr = kunden.kundennr
AND kunden.ortnr = orte.ortnr
AND fahrraeder.modellnr = modelle.modellnr
AND vermietnr =1;

# 4	
SELECT fahrraeder.fahrradnr, bezeichnung, DATEDIFF(bis, von) AS Mietdauer, tagesmietpreis
FROM fahrraeder, vermietungen, modelle
WHERE fahrraeder.fahrradnr = vermietungen.fahrradnr
AND fahrraeder.modellnr = modelle.modellnr
AND vermietnr =100;

# 5	
SELECT fahrraeder.fahrradnr, bezeichnung, DATEDIFF(bis, von) * tagesmietpreis AS Mietkosten
FROM fahrraeder, vermietungen, modelle
WHERE fahrraeder.fahrradnr = vermietungen.fahrradnr
AND fahrraeder.modellnr = modelle.modellnr
AND vermietnr =133;

# 6	
SELECT FORMAT(SUM(DATEDIFF(bis, von) * tagesmietpreis),2) AS Gesamtumsatz
FROM fahrraeder, vermietungen, modelle
WHERE fahrraeder.fahrradnr = vermietungen.fahrradnr
AND fahrraeder.modellnr = modelle.modellnr;

# 7	
SELECT vermietnr, von, bis,  DATEDIFF(bis, von) AS Mietdauer
FROM vermietungen
ORDER BY Mietdauer DESC;

# 8	
SELECT AVG(DATEDIFF(bis, von)) AS durchschnittliche_Mietdauer
FROM vermietungen;

# 9	
SELECT SUM(DATEDIFF(bis, von)) AS Gesamte_Mietdauer_aller_Fahrraeder
FROM vermietungen;

# 10	
SELECT fahrradnr, SUM(DATEDIFF(bis, von)) AS Gesamte_Mietdauer_aller_Fahrraeder
FROM vermietungen
GROUP BY fahrradnr
HAVING Gesamte_Mietdauer_aller_Fahrraeder > 40;

# 11	
SELECT vorname, nachname, SUM(DATEDIFF(bis, von)) AS Gesamte_Miettage_je_Kunde
FROM vermietungen, kunden
WHERE vermietungen.kundennr = kunden.kundennr
GROUP BY vorname, nachname
HAVING SUM(DATEDIFF(bis, von)) > 30
ORDER BY Gesamte_Miettage_je_Kunde DESC;

# 12	
SELECT vermietungen.kundennr, nachname, vorname, COUNT(vermietungen.kundennr) AS 	Anzahl_der_Vermietungen
FROM kunden, vermietungen
WHERE vermietungen.kundennr = kunden.kundennr
GROUP BY vermietungen.kundennr
HAVING Anzahl_der_Vermietungen > 5
ORDER BY Anzahl_der_Vermietungen DESC , kundennr;

# 13	
SELECT  kunden.kundennr, nachname, vorname, ort, strasse, COUNT(*) AS
 		Anzahl_der_Vermietungen
FROM kunden, vermietungen, orte
WHERE vermietungen.kundennr = kunden.kundennr
AND kunden.ortnr = orte.ortnr
GROUP BY kunden.kundennr, nachname, ort, strasse
HAVING ort='Freiburg'
AND Anzahl_der_Vermietungen > 5
ORDER BY Anzahl_der_Vermietungen;

# 14	
SELECT fahrraeder.fahrradnr, fahrradarten.bezeichnung, herstellername, COUNT(*) AS 	Anzahl_der_Vermietungen
FROM vermietungen, fahrraeder, modelle, hersteller, fahrradarten
WHERE vermietungen.fahrradnr = fahrraeder.fahrradnr
AND fahrraeder.modellnr = modelle.modellnr
AND modelle.herstellernr = hersteller.herstellernr
AND fahrradarten.artnr = modelle.artnr
GROUP BY fahrraeder.fahrradnr 
ORDER BY Anzahl_der_Vermietungen DESC;

# 15	
SELECT fahrradarten.bezeichnung, AVG(tagesmietpreis) AS durchschnittlicher_Mietpreis
FROM fahrraeder, modelle, fahrradarten
WHERE fahrraeder.modellnr = modelle.modellnr
AND fahrradarten.artnr = modelle.artnr
GROUP BY fahrradarten.bezeichnung;

# 16	
SELECT fahrradarten.bezeichnung, AVG(anschaffungswert) AS durchschnittliche_AK
FROM fahrraeder, modelle, fahrradarten
WHERE fahrraeder.modellnr = modelle.modellnr
AND fahrradarten.artnr = modelle.artnr
GROUP BY fahrradarten.bezeichnung;